"""empty message

Revision ID: 6c669d9ec3bd
Revises: 4b4823a384e7
Create Date: 2023-01-15 23:58:29.531456

"""
from datetime import datetime, timezone
from alembic import op
import sqlalchemy as sa
from sqlalchemy import orm
from app import db
import app.helpers.db_set_type

DeclarativeBase = orm.declarative_base()


# revision identifiers, used by Alembic.
revision = '6c669d9ec3bd'
down_revision = '6d641b08aaa8'
branch_labels = None
depends_on = None

class Category(DeclarativeBase):
    __tablename__ = 'category'
    id = sa.Column(sa.Integer, primary_key=True)
    household_id = sa.Column(sa.Integer, sa.ForeignKey('household.id'), nullable=True)
    
class Expense(DeclarativeBase):
    __tablename__ = 'expense'
    id = sa.Column(sa.Integer, primary_key=True)
    household_id = sa.Column(sa.Integer, sa.ForeignKey('household.id'), nullable=True)

class ExpenseCategory(DeclarativeBase):
    __tablename__ = 'expense_category'
    id = sa.Column(sa.Integer, primary_key=True)
    household_id = sa.Column(sa.Integer, sa.ForeignKey('household.id'), nullable=True)

class Item(DeclarativeBase):
    __tablename__ = 'item'
    id = sa.Column(sa.Integer, primary_key=True)
    household_id = sa.Column(sa.Integer, sa.ForeignKey('household.id'), nullable=True)

class Planner(DeclarativeBase):
    __tablename__ = 'planner'
    recipe_id = sa.Column(sa.Integer, primary_key=True)
    day = sa.Column(db.Integer, primary_key=True)
    household_id = sa.Column(sa.Integer, sa.ForeignKey('household.id'), nullable=True)
    
class Recipe(DeclarativeBase):
    __tablename__ = 'recipe'
    id = sa.Column(sa.Integer, primary_key=True)
    household_id = sa.Column(sa.Integer, sa.ForeignKey('household.id'), nullable=True)
    
class RecipeHistory(DeclarativeBase):
    __tablename__ = 'recipe_history'
    id = sa.Column(sa.Integer, primary_key=True)
    household_id = sa.Column(sa.Integer, sa.ForeignKey('household.id'), nullable=True)
    
class Shoppinglist(DeclarativeBase):
    __tablename__ = 'shoppinglist'
    id = sa.Column(sa.Integer, primary_key=True)
    household_id = sa.Column(sa.Integer, sa.ForeignKey('household.id'), nullable=True)
    
class Tag(DeclarativeBase):
    __tablename__ = 'tag'
    id = sa.Column(sa.Integer, primary_key=True)
    household_id = sa.Column(sa.Integer, sa.ForeignKey('household.id'), nullable=True)
    
class Settings(DeclarativeBase):
    __tablename__ = 'settings'
    id = sa.Column(sa.Integer)
    planner_feature = sa.Column('planner_feature', sa.BOOLEAN(), nullable=False, primary_key=True)
    expenses_feature = sa.Column('expenses_feature', sa.BOOLEAN(), nullable=False, primary_key=True)
    view_ordering = sa.Column('view_ordering', app.helpers.db_list_type.DbListType(), nullable=True)

class User(DeclarativeBase):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)
    owner = sa.Column('owner', sa.Boolean(), nullable=False)
    admin = sa.Column('admin', sa.Boolean(), nullable=False)
    expense_balance = sa.Column(sa.Float(), default=0, nullable=False)

class Household(DeclarativeBase):
    __tablename__ = 'household'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(128), unique=True)
    planner_feature = sa.Column(sa.Boolean(), primary_key=True, default=True)
    expenses_feature = sa.Column(sa.Boolean(), primary_key=True, default=True)
    view_ordering = sa.Column(app.helpers.db_list_type.DbListType(), default=list())
    created_at = sa.Column(sa.DateTime, nullable=False)
    updated_at = sa.Column(sa.DateTime, nullable=False)

class HouseholdMember(DeclarativeBase):
    __tablename__ = 'household_member'

    household_id = sa.Column(sa.Integer, sa.ForeignKey(
        'household.id'), primary_key=True)
    user_id = sa.Column(sa.Integer, sa.ForeignKey('user.id'), primary_key=True)
    owner = sa.Column(sa.Boolean(), default=False, nullable=False)
    admin = sa.Column(sa.Boolean(), default=False, nullable=False)
    expense_balance = sa.Column(sa.Float(), default=0, nullable=False)
    created_at = sa.Column(sa.DateTime, nullable=False)
    updated_at = sa.Column(sa.DateTime, nullable=False)



def upgrade():
    bind = op.get_bind()
    session = orm.Session(bind=bind)

    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('household',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=128), nullable=False),
    sa.Column('photo', sa.String(), nullable=True),
    sa.Column('language', sa.String(), nullable=True),
    sa.Column('planner_feature', sa.Boolean(), nullable=False),
    sa.Column('expenses_feature', sa.Boolean(), nullable=False),
    sa.Column('view_ordering', app.helpers.db_list_type.DbListType(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.Column('updated_at', sa.DateTime(), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_household')),
    )
    op.create_table('household_member',
    sa.Column('household_id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('owner', sa.Boolean(), nullable=False),
    sa.Column('admin', sa.Boolean(), nullable=False),
    sa.Column('expense_balance', sa.Float(), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.Column('updated_at', sa.DateTime(), nullable=False),
    sa.ForeignKeyConstraint(['household_id'], ['household.id'], name=op.f('fk_household_member_household_id_household')),
    sa.ForeignKeyConstraint(['user_id'], ['user.id'], name=op.f('fk_household_member_user_id_user')),
    sa.PrimaryKeyConstraint('household_id', 'user_id', name=op.f('pk_household_member'))
    )
    # Initial
    with op.batch_alter_table('category', schema=None) as batch_op:
        batch_op.add_column(sa.Column('household_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(batch_op.f('fk_category_household_id_household'), 'household', ['household_id'], ['id'])

    with op.batch_alter_table('expense', schema=None) as batch_op:
        batch_op.add_column(sa.Column('household_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(batch_op.f('fk_expense_household_id_household'), 'household', ['household_id'], ['id'])

    with op.batch_alter_table('expense_category', schema=None) as batch_op:
        batch_op.add_column(sa.Column('household_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(batch_op.f('fk_expense_category_household_id_household'), 'household', ['household_id'], ['id'])

    with op.batch_alter_table('item', schema=None) as batch_op:
        batch_op.add_column(sa.Column('household_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(batch_op.f('fk_item_household_id_household'), 'household', ['household_id'], ['id'])
        batch_op.drop_constraint('uq_item_name', type_='unique')

    with op.batch_alter_table('planner', schema=None) as batch_op:
        batch_op.add_column(sa.Column('household_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(batch_op.f('fk_planner_household_id_household'), 'household', ['household_id'], ['id'])

    with op.batch_alter_table('recipe', schema=None) as batch_op:
        batch_op.add_column(sa.Column('household_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(batch_op.f('fk_recipe_household_id_household'), 'household', ['household_id'], ['id'])

    with op.batch_alter_table('recipe_history', schema=None) as batch_op:
        batch_op.add_column(sa.Column('household_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(batch_op.f('fk_recipe_history_household_id_household'), 'household', ['household_id'], ['id'])

    with op.batch_alter_table('shoppinglist', schema=None) as batch_op:
        batch_op.add_column(sa.Column('household_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(batch_op.f('fk_shoppinglist_household_id_household'), 'household', ['household_id'], ['id'])
        batch_op.drop_constraint('uq_shoppinglist_name', type_='unique')

    with op.batch_alter_table('tag', schema=None) as batch_op:
        batch_op.add_column(sa.Column('household_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(batch_op.f('fk_tag_household_id_household'), 'household', ['household_id'], ['id'])

    with op.batch_alter_table('settings', schema=None) as batch_op:
        batch_op.add_column(sa.Column('id', sa.Integer(), nullable=True))

    # Data Migration
    settings = session.query(Settings).first()
    if settings:
        models: list = session.query(Category).all()\
            + session.query(Expense).all()\
            + session.query(ExpenseCategory).all()\
            + session.query(Item).all()\
            + session.query(Recipe).all()\
            + session.query(RecipeHistory).all()\
            + session.query(Shoppinglist).all()\
            + session.query(Tag).all()\
            + session.query(Planner).all()
        for model in models:
            model.household_id = 1

        household = Household()
        household.id = 1
        household.name = "Home"
        household.planner_feature = settings.planner_feature
        household.expenses_feature = settings.expenses_feature
        household.view_ordering = settings.view_ordering
        household.created_at = datetime.now(timezone.utc)
        household.updated_at = datetime.now(timezone.utc)
        
        users = session.query(User).all()
        for user in users:
            hm = HouseholdMember()
            hm.created_at = datetime.now(timezone.utc)
            hm.updated_at = datetime.now(timezone.utc)
            hm.user_id = user.id
            hm.household_id = 1
            hm.admin = user.admin
            hm.owner = user.owner
            hm.expense_balance = user.expense_balance
            models.append(hm)
            user.admin = user.admin or user.owner

        models.append(household)
        models += users
        
        try:
            session.delete(settings)
            session.bulk_save_objects(models)
            session.commit()
        except Exception as e:
            session.rollback()
            raise e


    # Final
    with op.batch_alter_table('category', schema=None) as batch_op:
        batch_op.alter_column('household_id', nullable=False)

    with op.batch_alter_table('expense', schema=None) as batch_op:
        batch_op.alter_column('household_id', nullable=False)

    with op.batch_alter_table('expense_category', schema=None) as batch_op:
        batch_op.alter_column('household_id', nullable=False)

    with op.batch_alter_table('item', schema=None) as batch_op:
        batch_op.alter_column('household_id', nullable=False)

    with op.batch_alter_table('planner', schema=None) as batch_op:
        batch_op.alter_column('household_id', nullable=False)

    with op.batch_alter_table('recipe', schema=None) as batch_op:
        batch_op.alter_column('household_id', nullable=False)

    with op.batch_alter_table('recipe_history', schema=None) as batch_op:
        batch_op.alter_column('household_id', nullable=False)

    with op.batch_alter_table('shoppinglist', schema=None) as batch_op:
        batch_op.alter_column('household_id', nullable=False)

    with op.batch_alter_table('tag', schema=None) as batch_op:
        batch_op.alter_column('household_id', nullable=False)

    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.drop_column('owner')
        batch_op.drop_column('expense_balance')

    with op.batch_alter_table('settings', schema=None) as batch_op:
        batch_op.alter_column('id', nullable=False)
        batch_op.drop_column('view_ordering')
        batch_op.drop_column('expenses_feature')
        batch_op.drop_column('planner_feature')

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.add_column(sa.Column('expense_balance', sa.FLOAT(), nullable=True))
        batch_op.add_column(sa.Column('owner', sa.BOOLEAN(), nullable=True))

    with op.batch_alter_table('tag', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_tag_household_id_household'), type_='foreignkey')
        batch_op.drop_column('household_id')

    with op.batch_alter_table('shoppinglist', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_shoppinglist_household_id_household'), type_='foreignkey')
        batch_op.drop_column('household_id')
        batch_op.create_unique_constraint(batch_op.f('uq_shoppinglist_name'), ['name'])

    with op.batch_alter_table('settings', schema=None) as batch_op:
        batch_op.add_column(sa.Column('planner_feature', sa.BOOLEAN(), nullable=False))
        batch_op.add_column(sa.Column('expenses_feature', sa.BOOLEAN(), nullable=False))
        batch_op.add_column(sa.Column('view_ordering', sa.VARCHAR(), nullable=True))
        batch_op.drop_column('id')

    with op.batch_alter_table('recipe_history', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_recipe_history_household_id_household'), type_='foreignkey')
        batch_op.drop_column('household_id')

    with op.batch_alter_table('recipe', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_recipe_household_id_household'), type_='foreignkey')
        batch_op.drop_column('household_id')

    with op.batch_alter_table('item', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_item_household_id_household'), type_='foreignkey')
        batch_op.drop_column('household_id')
        batch_op.create_unique_constraint(batch_op.f('uq_item_name'), ['name'])

    with op.batch_alter_table('expense_category', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_expense_category_household_id_household'), type_='foreignkey')
        batch_op.drop_column('household_id')

    with op.batch_alter_table('expense', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_expense_household_id_household'), type_='foreignkey')
        batch_op.drop_column('household_id')

    with op.batch_alter_table('category', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_category_household_id_household'), type_='foreignkey')
        batch_op.drop_column('household_id')

    op.drop_table('household_member')
    op.drop_table('household')
    # ### end Alembic commands ###
